/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.sample.product;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.sadhar.sia.common.ClassConnection;
import org.sadhar.sia.common.ClassUtility;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author hendro
 */
public class ProductDAOImpl implements ProductDAO {

    public ProductDAOImpl() {
        ClassConnection.getTransactionProxyFactoryBean().setTarget(this);
    }

    public List<Product> list() throws Exception {
        List<Product> products = new ArrayList<Product>();
        List<Map> row = ClassConnection.getJdbc().queryForList("SELECT * FROM dbproduct.product");
        for (Map m : row) {
            Product product = new Product();
            product.setId(Integer.valueOf(m.get("id").toString()));
            product.setCategoryId(Integer.valueOf(m.get("category_id").toString()));
            product.setName(m.get("name").toString());
            product.setPrice(Double.valueOf(m.get("price").toString()));
            product.setDiscountId(Integer.valueOf(m.get("discount_id").toString()));
            product.setDescription(m.get("description").toString());
            products.add(product);
        }
        return products;
    }

    public void insert(Product product) throws Exception {
        String sql = "INSERT INTO " +
                "dbproduct.product(category_id,name,price,discount_id,description) " +
                "VALUES(?,?,?,?,?)";
        ClassConnection.getJdbc().update(sql, new Object[]{
                    Integer.valueOf(product.getCategoryId()),
                    ClassUtility.quote(product.getName()),
                    Double.valueOf(product.getPrice()),
                    Integer.valueOf(product.getDiscountId()),
                    ClassUtility.quote(product.getDescription())
                });
        System.out.println("OK");
    }

    public void update(Product product) throws Exception {
        String sql = "UPDATE dbproduct.product SET category_id=?,name=?,price=?,discount_id=?,description=? " +
                "WHERE id=?";
        ClassConnection.getJdbc().update(sql, new Object[]{
                    Integer.valueOf(product.getCategoryId()),
                    ClassUtility.quote(product.getName()),
                    Double.valueOf(product.getPrice()),
                    Integer.valueOf(product.getDiscountId()),
                    ClassUtility.quote(product.getDescription()),
                    Integer.valueOf(product.getId())
                });
    }

    public void delete(Product product) throws Exception {
        String sql = "DELETE FROM dbproduct.product WHERE id=?";
        ClassConnection.getJdbc().update(sql, new Object[]{
                    Integer.valueOf(product.getId())
                });
    }

    public List<Product> select(String name) throws Exception {
        String sql = "SELECT * FROM dbproduct.product WHERE name=?";
        return ClassConnection.getJdbc().queryForList(sql, new Object[]{
                    name
                });
    }

    public List<Product> select(int id, String name) throws Exception {
        String sql = "SELECT * FROM dbproduct.product WHERE id<>? AND name=?";
        return ClassConnection.getJdbc().queryForList(sql, new Object[]{
                    Integer.valueOf(id),
                    name
                });
    }
}
